from datetime import datetime
from sqlalchemy.orm import relationship
from flask_sqlalchemy import SQLAlchemy
import os,sys
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from extetion import db

# 定义film_imfor表模型
class FilmInfor(db.Model):
    __tablename__ = 'film_imfor'

    film_id = db.Column(db.Integer, primary_key=True)
    film_name = db.Column(db.String(100), nullable=False)
    actor = db.Column(db.String(500), nullable=False)
    director = db.Column(db.String(500), nullable=False)
    release_time = db.Column(db.String(100), nullable=False)
    film_mark = db.Column(db.Integer, nullable=False)
    introductory = db.Column(db.String(2000), nullable=False)
    comment_num = db.Column(db.Integer, nullable=False)
    long_comment_num = db.Column(db.Integer, nullable=False)
    love_num = db.Column(db.Integer, nullable=False)
    assist_num = db.Column(db.Integer, nullable=False)
    watch_num = db.Column(db.Integer, nullable=False)
    www_http = db.Column(db.String(200))
    mark_sum = db.Column(db.Integer, nullable=True)
    mark_num = db.Column(db.Integer, nullable=True)
    
    def to_dict(self):
        return {
            'film_id': self.film_id,
            'film_name': self.film_name,
            'actor': self.actor,
            'director': self.director,
            'release_time': self.release_time,
            'film_mark': self.film_mark,
            'introductory': self.introductory,
            'comment_num': self.comment_num,
            'long_comment_num': self.long_comment_num,
            'love_num': self.love_num,
            'assist_num': self.assist_num,
            'watch_num': self.watch_num,
            'www_http': self.www_http,
            'mark_sum':self.mark_sum,
            'mark_num':self.mark_num
        }

# 定义comment_on_film表模型
class CommentOnFilm(db.Model):
    __tablename__ = 'comment_on_film'

    film_comment_id = db.Column(db.Integer, primary_key=True)
    film_id = db.Column(db.Integer, db.ForeignKey('film_imfor.film_id'), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.user_id'), nullable=False)
    long_comment = db.Column(db.Text, nullable=False)
    comment_date = db.Column(db.DateTime, nullable=False)
    comment_assist = db.Column(db.Integer, nullable=False)
    top_not = db.Column(db.Boolean, nullable=False)
    comment_pass = db.Column(db.Boolean, nullable=False)
    
    
    def to_dict(self):
        return {
            'film_comment_id': self.film_comment_id,
            'film_id': self.film_id,
            'user_id': self.user_id,
            'long_comment': self.long_comment,
            'comment_date': self.comment_date,
            'comment_assist': self.comment_assist,
            'top_not': self.top_not,
            'comment_pass': self.comment_pass
        }

# 定义manager表模型
class Manager(db.Model):
    __tablename__ = 'manager'

    manager_id = db.Column(db.Integer, primary_key=True)
    manager_password = db.Column(db.String(20), nullable=False)
    manager_name = db.Column(db.String(20), nullable=False)
    manager_email = db.Column(db.String(40), nullable=False)
    
    def to_dict(self):
        return {
            'manager_id': self.manager_id,
            'manager_password': self.manager_password,
            'manager_name': self.manager_name,
            'manager_email': self.manager_email
        }
        
# 定义mark_commend表模型
class MarkCommend(db.Model):
    __tablename__ = 'mark_commend'

    mark_id = db.Column(db.Integer, primary_key=True)
    film_id = db.Column(db.Integer, db.ForeignKey('film_imfor.film_id'), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.user_id'), nullable=False)
    mark = db.Column(db.Integer, nullable=False)
    short_comment = db.Column(db.String(200), nullable=False)
    short_assist = db.Column(db.Integer, nullable=False)
    short_top_not = db.Column(db.Boolean, nullable=False)
    
    
    
    def to_dict(self):
        return {
            'mark_id': self.mark_id,
            'film_id': self.film_id,
            'user_id': self.user_id,
            'mark': self.mark,
            'short_comment': self.short_comment,
            'short_assist': self.short_assist,
            'short_top_not': self.short_top_not
        }

# 定义recomment表模型
class Recomment(db.Model):
    __tablename__ = 'recomment'

    recomment_id = db.Column(db.Integer, primary_key=True)
    mark_id = db.Column(db.Integer, db.ForeignKey('mark_commend.mark_id'), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.user_id'), nullable=False)
    recomment = db.Column(db.String(200), nullable=False)
    re_assist = db.Column(db.Integer, nullable=False)
    re_top_not = db.Column(db.Boolean, nullable=False)
    
    
    def to_dict(self):
        return {
            'recomment_id': self.recomment_id,
            'mark_id': self.mark_id,
            'user_id': self.user_id,
            'recomment': self.recomment,
            're_assist': self.re_assist,
            're_top_not': self.re_top_not
        }

# 定义supermanager表模型
class SuperManager(db.Model):
    __tablename__ = 'supermanager'

    super_ma_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    super_password = db.Column(db.String(20), nullable=False)
    all_film_num = db.Column(db.Integer, nullable=False)
    all_user_num = db.Column(db.Integer, nullable=False)
    all_manager_num = db.Column(db.Integer, nullable=False)
    
    @staticmethod
    def get_super_manager():
        # 只存在一个SuperManager记录
        return SuperManager.query.first()

# 定义u_love_f表模型
class ULoveF(db.Model):
    __tablename__ = 'u_love_f'

    user_id = db.Column(db.Integer, db.ForeignKey('user.user_id'), primary_key=True)
    film_id = db.Column(db.Integer, db.ForeignKey('film_imfor.film_id'), primary_key=True)
    
    
    def to_dict(self):
        return {
            'user_id':self.user_id,
            'film_id':self.film_id
        }
    
# 定义user表模型
class User(db.Model):
    __tablename__ = 'user'

    user_id = db.Column(db.Integer, primary_key=True)
    user_password = db.Column(db.String(20), nullable=False)
    user_name = db.Column(db.String(20), nullable=False)
    ban_not = db.Column(db.Boolean, nullable=False)
    ban_reason = db.Column(db.String(200), nullable=True)
    ban_ma_id = db.Column(db.Integer, nullable=True)
    block_not = db.Column(db.Boolean, nullable=False)
    block_reason = db.Column(db.String(200), nullable=True)
    block_ma_id = db.Column(db.Integer, nullable=True)
    complain_not = db.Column(db.Boolean, nullable=False)
    user_email = db.Column(db.String(40), nullable=False)
    complain_reason = db.Column(db.String(200), nullable=True)
    
    
    def to_dict(self):
        return {
            'user_id': self.user_id,
            'user_password': self.user_password,
            'user_name': self.user_name,
            'ban_not': self.ban_not,
            'ban_reason': self.ban_reason,
            'ban_ma_id': self.ban_ma_id,
            'block_not': self.block_not,
            'block_reason': self.block_reason,
            'block_ma_id': self.block_ma_id,
            'complain_not': self.complain_not,
            'user_email': self.user_email,
            'complain_reason':self.complain_reason
        }

def get_love_film(user_id):
        loved_films = db.session.query(FilmInfor).join(ULoveF, FilmInfor.film_id == ULoveF.film_id).filter(ULoveF.user_id == user_id).all()
        loved_films_list = [film.to_dict() for film in loved_films]
        return loved_films_list